package cn.joeg.app.web.db.util;

import com.alibaba.druid.util.JdbcUtils;
import org.apache.commons.dbutils.DbUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;


@Component
public class DBCommon {

//    private static final Logger logger = LoggerFactory.getLogger(DBCommon.class);


    @Autowired
    @Qualifier("dataSource")
    private DataSource dataSource;


    public List<Map<String, Object>> query(String sql) throws SQLException {
        return JdbcUtils.executeQuery(dataSource, sql, Collections.emptyList());
    }


    public List<Map<String, Object>> query(String sql, Object... params) throws SQLException {
        if (params == null) {
            return query(sql);
        }
        return JdbcUtils.executeQuery(dataSource, sql, Arrays.asList(params));
    }


    public Object getOneColumn(String sql, Object... params) throws SQLException {
        Connection conn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            pstat = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstat.setObject(i + 1, params[i]);
                }
            }
            rs = pstat.executeQuery();
            if (rs.next()) {
                return rs.getObject(1);
            }
            return null;
        } finally {
            DbUtils.closeQuietly(conn, pstat, rs);
        }
    }

    public int update(final String sql) throws SQLException {
        return JdbcUtils.executeUpdate(dataSource, sql, Collections.emptyList());
    }


    public int update(final String sql, final Object... params) throws SQLException {
        if (params == null) {
            return update(sql);
        }
        return JdbcUtils.executeUpdate(dataSource, sql, Arrays.asList(params));
    }

    public Object insertAndReturnKey(final String sql) throws SQLException {
        Connection conn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            pstat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstat.execute();
            rs = pstat.getGeneratedKeys();
            if (rs.next()) {
                return rs.getObject(1);
            }
            return null;
        } finally {
            DbUtils.closeQuietly(conn, pstat, rs);
        }
    }

    public Object insertAndReturnKey(final String sql, final Object... params) throws SQLException {
        Connection conn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            pstat = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstat.setObject(i + 1, params[i]);
                }
            }
            pstat.execute();
            rs = pstat.getGeneratedKeys();
            if (rs.next()) {
                return rs.getObject(1);
            }
            return null;
        } finally {
            DbUtils.closeQuietly(conn, pstat, rs);
        }
    }

    public int[] batchUpdate(final List<String> sqls) {
        int[] result;
        Statement stat = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            stat = conn.createStatement();
            for (String sql : sqls) {
                stat.addBatch(sql);
            }
            result = stat.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            try {
                DbUtils.rollback(conn);
            } catch (SQLException e1) {
                throw new ExceptionInInitializerError(e1);
            }
            throw new ExceptionInInitializerError(e);
        } finally {
            DbUtils.closeQuietly(conn, stat, null);
        }
        return result;
    }


}
